package com.project.dao.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.Query;
import org.springframework.stereotype.Repository;

import com.project.dao.BookInfoDAO;
import com.project.dto.CountBookType;
import com.project.entity.BookInfo;
import com.project.entity.BookType;

@Repository("bookInfoDAO")
public class BookInfoDAOImpl extends GenericDAOImpl<BookInfo, Integer>
		implements BookInfoDAO {

	@SuppressWarnings("unchecked")
	@Override
	public List<BookInfo> getBookList(Object object) {
		String hql = "select b.id, b.name,t.name,b.author, b.publisher, b.price,b.city,b.totalQuantity,b.comment,b.deleteDateTime"
				+ " from BookInfo b,BookType t where b.bookType.id = t.id and b.name like ?";
		Query query = getSession().createQuery(hql);
		query.setString(0, "%" + object.toString() + "%");
		List<Object[]> list = (List<Object[]>) query.list();
		List<BookInfo> booklist = new ArrayList<BookInfo>();
		for (Object[] objects : list) {
			BookInfo bookInfo = new BookInfo();
			BookType bookType = new BookType();
			if (objects[0] != null) {
				bookInfo.setId((Integer) objects[0]);
			}
			if (objects[1] != null) {
				bookInfo.setName(objects[1].toString());
			}
			if (objects[2] != null) {
				bookType.setName(objects[2].toString());
				bookInfo.setBookType(bookType);
			}
			if (objects[3] != null) {
				bookInfo.setAuthor(objects[3].toString());
			}
			if (objects[4] != null) {
				bookInfo.setPublisher(objects[4].toString());
			}
			if (objects[5] != null) {
				bookInfo.setPrice((Float) objects[5]);
			}
			if (objects[6] != null) {
				bookInfo.setCity(objects[6].toString());
			}
			if (objects[7] != null) {
				bookInfo.setTotalQuantity((Integer) objects[7]);
			}
			if (objects[8] != null) {
				bookInfo.setComment(objects[8].toString());
			}
			if (objects[9] != null) {
				bookInfo.setDeleteDateTime((Date) objects[9]);
			} else {
				bookInfo.setDeleteDateTime(null);
			}
			booklist.add(bookInfo);
		}
		return booklist;
	}

	@Override
	public List<Integer> availableQuan(String queryParams) {
		List<Integer> aqList = new ArrayList<Integer>();
		List<BookInfo> totalQuantityList = getBookList(queryParams);
		for (BookInfo objects : totalQuantityList) {
			String bookName = objects.getName();
			int totalQuantity = Integer.parseInt(objects.getTotalQuantity()
					.toString());
			String borrQuantityString = "select count(*) from BorrowReturnInfo where bookInfo.name = ?";
			Query borrQuantityQuery = getSession().createQuery(
					borrQuantityString);
			borrQuantityQuery.setString(0, bookName);
			Object borrQuantity = borrQuantityQuery.uniqueResult();
			int availableQuantity = totalQuantity
					- Integer.parseInt(borrQuantity.toString());
			aqList.add(availableQuantity);
		}
		return aqList;
	}

	@Override
	public BookInfo get(String name) {
		String hql = "from BookInfo where name=?";
		BookInfo bookInfo = executeUniqueQuery(hql, name);
		return bookInfo;
	}

	@Override
	public BookInfo verifyBookInfo(Object... objects) {
		String hql = "from BookInfo where name=? and author=? and publisher=?";
		return executeUniqueQuery(hql, objects);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<CountBookType> countBookType() {
		String hql1 = "select count(*) from BookInfo";
		Integer totalCount = Integer.valueOf(getSession().createQuery(hql1)
				.uniqueResult().toString());
		String hql2 = "select bookType.name, count(*) from BookInfo group by bookType.id order by count(*) desc";
		List<Object[]> objects = (List<Object[]>) getSession()
				.createQuery(hql2).list();
		List<CountBookType> list = new ArrayList<CountBookType>();

		for (Object[] obj : objects) {
			Integer amount = Integer.parseInt(obj[1].toString());
			Integer percent = amount * 100 / totalCount;

			CountBookType countBookType = new CountBookType();
			countBookType.setName(obj[0].toString());
			countBookType.setAmount(amount);
			countBookType.setPercent(percent);
			list.add(countBookType);
		}

		return list;
	}

	@Override
	public List<BookInfo> findByPage(Integer offset, Integer pageSize) {
		return findByPage("from BookInfo", offset, pageSize);
	}

	@Override
	public List<BookInfo> searchByType(Integer btypeId, Integer offset,
			Integer pageSize) {
		String hql = "from BookInfo where bookType.id=?";
		return findByPage(hql, offset, pageSize, btypeId);
	}

	@Override
	public List<BookInfo> searchByName(String bookName, Integer offset,
			Integer pageSize) {
		String hql = "from BookInfo where name like ?";
		String name = "%" + bookName + "%";
		return findByPage(hql, offset, pageSize, name);
	}

	@Override
	public List<BookInfo> searchByNameAndType(String bookName, Integer btypeId,
			Integer offset, Integer pageSize) {
		String hql = "from BookInfo where name like ? and bookType.id = ?";
		String name = "%" + bookName + "%";
		return findByPage(hql, offset, pageSize, name, btypeId);
	}

	@Override
	public Integer totalCount(String bookName, Integer btypeId) {
		String name = "%" + bookName + "%";
		if (StringUtils.isBlank(bookName) && btypeId == 0) {
			String hql = "select count(*) from BookInfo";
			return Integer.parseInt(getSession().createQuery(hql)
					.uniqueResult().toString());
		} else if (StringUtils.isBlank(bookName) && btypeId > 0) {
			String hql = "select count(*) from BookInfo where bookType.id=?";
			return Integer.parseInt(getSession().createQuery(hql)
					.setParameter(0, btypeId).uniqueResult().toString());
		} else if (StringUtils.isNotBlank(bookName) && btypeId == 0) {
			String hql = "select count(*) from BookInfo where name like ?";
			return Integer.parseInt(getSession().createQuery(hql)
					.setParameter(0, name).uniqueResult().toString());
		} else if (StringUtils.isNotBlank(bookName) && btypeId > 0) {
			String hql = "select count(*) from BookInfo where name like ? and bookType.id = ?";
			return Integer.parseInt(getSession().createQuery(hql)
					.setParameter(0, name).setParameter(1, btypeId)
					.uniqueResult().toString());
		}
		return 0;
	}

}
